﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[tblMSX_server_discovery]
#-- Purpose:		Stores a list of all servers discovered on the network.
#--	Last Update:	04/06/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE TABLE [dbo].[tblMSX_server_discovery]
(
	ssd_id							int	IDENTITY (1, 1) NOT NULL,
	ssd_sqlserver_name_ro			AS ([ssd_server_name]+case when [ssd_instance_name] IS NULL then '' else '\'+[ssd_instance_name] end),
	ssd_has_access_ro				AS ([dbo].[fnMSX_has_access]([ssd_sql_service],[ssd_agent_service],[ssd_sql_start_mode],[ssd_sql_state])),
	ssd_type_ro						AS ([dbo].[fnMSX_get_environment_type]([ssd_server_name], [ssd_instance_name], [ssd_is_msx_server])),
	ssd_is_msx_server               bit                             NOT NULL CONSTRAINT [DF_tblMSX_server_discovery_isMSX] DEFAULT((0)),
	ssd_is_disabled					bit								NOT NULL CONSTRAINT [DF_tblMSX_server_discovery_isDisabled] DEFAULT((0)),
	ssd_is_vendor_managed_ro		AS ([dbo].[fnMSX_is_vendor_managed]([ssd_server_name])),
	ssd_deploy_msx_backup			bit								NOT NULL CONSTRAINT [DF_tblMSX_server_discovery_DeployBackup] DEFAULT((1)),
	ssd_deploy_msx_index			bit								NOT NULL CONSTRAINT [DF_tblMSX_server_discovery_DeployIndex] DEFAULT((1)),
	ssd_last_update					datetime						NOT NULL,
	ssd_last_error					datetime						NULL,
	ssd_date_discovered				datetime						NULL,
	ssd_server_name					varchar(60)						NOT NULL,
	ssd_instance_name				varchar(60)						NULL,
	ssd_is_mission_critical			bit								NOT NULL CONSTRAINT [DF_tblMSX_server_discovery_MissionCritical] DEFAULT((0)),
	ssd_sql_service					varchar(255)					NULL,
	ssd_agent_service				varchar(255)					NULL,
	ssd_sql_start_mode				varchar(10)						NULL,
	ssd_sql_state					varchar(20)						NULL,
	ssd_agent_start_mode			varchar(10)						NULL,
	ssd_agent_state					varchar(20)						NULL,
	ssd_pwrshl_script_dir           varchar(2000)                   NULL, 
	[ssd_cluster_name]				varchar(20)						NULL, 
    [ssd_app_id]					int								NULL, 
    CONSTRAINT						PK_tblMSX_server_discovery		PRIMARY KEY CLUSTERED (ssd_id ASC), 
    CONSTRAINT [FK_tblMSX_server_discovery_application] FOREIGN KEY ([ssd_app_id]) REFERENCES [dbo].[tblMSX_application]([app_id])
)
GO
CREATE INDEX [IX_tblMSX_server_discovery_Name] ON [dbo].[tblMSX_server_discovery] ([ssd_server_name], [ssd_instance_name])
GO
CREATE INDEX [IX_tblMSX_server_discovery_sqlName] ON [dbo].[tblMSX_server_discovery] ([ssd_sqlserver_name_ro])
GO
CREATE INDEX [IX_tblMSX_server_discovery_ClusterName] ON [dbo].[tblMSX_server_discovery] ([ssd_cluster_name])
GO